﻿CREATE PROCEDURE [dbo].[proc_CallCenter_Tongji_Emp]
	(
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@DepartmentId int,
		@CompanyId int
	)
AS
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59');

	If @DepartmentId > 0
		Select Id,CompanyId,EName,UserName,ZXNum,FJNum,
			IsNull((Select Count(0) From CallCenter_Record Where CompanyId=@CompanyId And ZuoxiID=ZXNum and EndDate>=@sRq And EndDate<=@eRq and [Type]='CH_CALLOUT'),0) As OutCount,
			IsNull((Select Count(0) From CallCenter_Record Where CompanyId=@CompanyId And ZuoxiID=ZXNum and EndDate>=@sRq And EndDate<=@eRq and [Type]='CH_PHONEIN'),0) As InCount
		From CallCenter
		Where CompanyId=@CompanyId And DepartmentId=@DepartmentId
		Order By FJNum
	Else
		WITH List As(
			Select ZuoxiID,Count(0) As Shl1,0 As Shl2
			From CallCenter_Record
			Where [CompanyId]=@CompanyId and DepartmentId=-1 And [Type]='CH_PHONEIN'
			And CreateDate>=@sRq And CreateDate<=@eRq
			Group By ZuoxiID
			Union All
			Select ZuoxiID,0 As Shl1,Count(0) As Shl2
			From CallCenter_Record
			Where CompanyId=@CompanyId and DepartmentId=-1 And [Type]='CH_CALLOUT'
			And CreateDate>=@sRq And CreateDate<=@eRq
			Group By ZuoxiID
		)
		Select 0 As Id,@CompanyId As CompanyId,'无' EName,ZuoxiID As UserName,0 As ZXNum,ZuoxiID As FJNum,
		Sum(Shl1) As InCount,Sum(Shl2) As OutCount
		From List
		Group By ZuoxiID
		Order By FJNum
End
